
clear all
cap log close
local user "awcassidy1"
import delimited "C:\Users/`user'\Dropbox\jmp_new\Austin Electricity and Gas Statistics\Residential_Average_Monthly_kWh_and_Bills.csv", ///
	clear

gen datemonthstarting=date(monthstarting, "MDYhm")

gen salemonth=month(datemonthstarting)
gen saleyear=year(datemonthstarting)

drop datemonthstarting monthstarting

destring averagebill, ignore("$") replace

rename fuel* austinelec 

gen avg_dollars_per_kwh =  averagebill/averagekwh

la var avg_dollars_per_kwh "Average dollars per KWH"
gen elec_charge_per_mmbtu=avg_dollars_per_kwh*(1/0.003412)


keep salemonth saleyear avg_dollars_per_kwh elec_charge_per_mmbtu 

sort saleyear salemonth, stable

save "C:\Users/`user'\Dropbox\jmp_new\Austin Electricity and Gas Statistics\elec_average.dta", replace

import excel "C:\Users/`user'\Dropbox\jmp_new\Austin Electricity and Gas Statistics\nat gas.xls", ///
	sheet("Data 1") firstrow cellrange(A3:B344) clear
	
gen salemonth=month(Date)
gen saleyear=year(Date)

rename Texas* texasgas

drop Date

sort saleyear salemonth, stable

*adjust for MMBTU to MCF. according to EIA: https://www.eia.gov/tools/faqs/faq.php?id=45&t=8#:~:text=One%20thousand%20cubic%20feet%20(Mcf,1.037%20MMBtu%2C%20or%2010.37%20therms.
gen gas_charge_per_mmbtu=texasgas*(1/1.037)

keep salemonth saleyear gas_charge_per_mmbtu
 

save "C:\Users/`user'\Dropbox\jmp_new\Austin Electricity and Gas Statistics\nat_gas_average.dta", replace